package com.ys.controller.api.report;

import com.alibaba.fastjson.JSONObject;
import com.ys.entity.report.ReDeptRevenueExpenditure;
import com.ys.entity.sys.YsCol;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService.UploadDto;
import com.ys.util.ExportExcelUtils;
import com.ys.util.admin.ConditionUtil;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * 交易类型业务收支报告
 */
@Controller
@RequestMapping("/api/inter/reDeptRevenueExpenditureByDealType")
public class ApiInterReDeptRevenueExpenditureByDealTypeController {
    private static final Logger logger = LoggerFactory.getLogger(ApiInterReDeptRevenueExpenditureByDealTypeController.class);
    private static Long MENU_ID = 10159L;

    @Autowired
    private CommonApiService<ReDeptRevenueExpenditure> commonApiService;


    public String getMainTable(){
        String mainTable = "SELECT " +
                "R.CountType,R.DealType,R.YearNum,R.AccountType," +
                "'' AS Id,'' AS AccountCode,'' AS AccountName," +
                "'' AS MonthLastDayCount,'' AS Status, '' AS CreateTime, '' AS UpdateTime," +
                "ISNUll(SUM(R.JanCount),0) AS JanCount," +
                "ISNUll(SUM(R.FebCount),0) AS FebCount," +
                "ISNUll(SUM(R.MarCount),0) AS MarCount," +
                "ISNUll(SUM(R.AprCount),0) AS AprCount," +
                "ISNUll(SUM(R.MayCount),0) AS MayCount," +
                "ISNUll(SUM(R.JunCount),0) AS JunCount," +
                "ISNUll(SUM(R.JulCount),0) AS JulCount," +
                "ISNUll(SUM(R.AugCount),0) AS AugCount," +
                "ISNUll(SUM(R.SeptCount),0) AS SeptCount," +
                "ISNUll(SUM(R.OctCount),0) AS OctCount," +
                "ISNUll(SUM(R.NovCount),0) AS NovCount," +
                "ISNUll(SUM(R.DecCount),0) AS DecCount," +
                "ISNUll(SUM(R.YearCount),0) AS YearCount " +
                "FROM [kjecp_report].[dbo].RE_DeptRevenueExpenditure R " +
                "GROUP BY R.CountType,R.YearNum,R.DealType,R.AccountType ";
        return mainTable;
    }

    /**
     * 设置查询的cond的sql设置
     * @param cond
     * @return
     */
    public ReDeptRevenueExpenditure setSeachSql(ReDeptRevenueExpenditure cond){
        String mainTable = getMainTable();
        cond.setMainTable("("+mainTable+")");
        cond.where("A.YearCount !=0");
        //排序
        cond.setOrderby("A.CountType ASC,A.DealType ASC,A.AccountType ASC");
        return  cond;
    }


    /**
     * 查询
     */
    @RequiresPermissions("reDeptRevenueExpenditureByDealType:view")
    @RequestMapping("/search")
    @ResponseBody
    public LayuiTableVoJA search(Model model, HttpServletRequest request, HttpServletResponse response,
                                 @ModelAttribute ReDeptRevenueExpenditure cond
    ){
        try {
            cond = setSeachSql(cond);
            LayuiTableVoJA tableVoJA = commonApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                String mainTable = getMainTable();
                cond.setMainTable("("+mainTable+")");
                // 合计
                JO.put("janCount", cond.setFields("ISNULL(SUM(A.JanCount),0)").sumDecimal());
                JO.put("febCount", cond.setFields("ISNULL(SUM(A.FebCount),0)").sumDecimal());
                JO.put("marCount", cond.setFields("ISNULL(SUM(A.MarCount),0)").sumDecimal());
                JO.put("aprCount", cond.setFields("ISNULL(SUM(A.AprCount),0)").sumDecimal());
                JO.put("mayCount", cond.setFields("ISNULL(SUM(A.MayCount),0)").sumDecimal());
                JO.put("junCount", cond.setFields("ISNULL(SUM(A.JunCount),0)").sumDecimal());
                JO.put("julCount", cond.setFields("ISNULL(SUM(A.JulCount),0)").sumDecimal());
                JO.put("augCount", cond.setFields("ISNULL(SUM(A.AugCount),0)").sumDecimal());
                JO.put("septCount", cond.setFields("ISNULL(SUM(A.SeptCount),0)").sumDecimal());
                JO.put("octCount", cond.setFields("ISNULL(SUM(A.OctCount),0)").sumDecimal());
                JO.put("novCount", cond.setFields("ISNULL(SUM(A.NovCount),0)").sumDecimal());
                JO.put("decCount", cond.setFields("ISNULL(SUM(A.DecCount),0)").sumDecimal());
                JO.put("yearCount", cond.setFields("ISNULL(SUM(A.YearCount),0)").sumDecimal());
                tableVoJA.setTotalRow(JO);
            }
            return tableVoJA;
        }catch (Exception e){
            logger.error("查询部门业务收支出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }


    /**
     * 导出
     */
    @RequestMapping("/export")
    @ResponseBody
    public R export(String conditions){
        try {
            String yearNum = ConditionUtil.getConditionValue(conditions, "yearNum");//年份
            String countType = ConditionUtil.getConditionValue(conditions, "countType");//类型
            String dealType = ConditionUtil.getConditionValue(conditions, "dealType");//交易类型
            String whereString = "A.YearCount !=0 ";
            if(yearNum!=null && !"".equals(yearNum)){
                whereString += " AND A.YearNum = "+yearNum;
            }
            if(countType!=null && !"".equals(countType)){
                whereString += " AND A.CountType = "+countType;
            }
            if(dealType!=null && !"".equals(dealType)){
                whereString += " AND A.DealType Like '%"+dealType+"%' ";
            }

            //获取菜单字段数据
            List<YsCol> listField = new YsCol().setMenuId(MENU_ID).where("A.Code != 'ope' AND A.Code != 'countType' AND A.Code != 'accountName'").setOrderby("A.SortNo asc").queryList();
            //获取数据
            String mainTable = getMainTable();
            ReDeptRevenueExpenditure cond = new ReDeptRevenueExpenditure()
                    .setMainTable("("+mainTable+")")
                    .addField("CASE WHEN A.CountType = 0 THEN '收入' WHEN A.CountType = 1 THEN '支出' ELSE '利润' END countTypeName")
                    .where(whereString)
                    .setOrderby("A.CountType ASC,A.DealType ASC");
            //权限
            cond = commonApiService.dataAuth(MENU_ID, cond);
            List<Map<String, Object>> listData = cond.queryMap();
            /*
            List<Map<String, Object>> listData = new ReDeptRevenueExpenditure()
                    .setMainTable("("+mainTable+")")
                    .addField("A.AccountCode + '/' + A.AccountName AS accountNameTxt ")
                    .addField("CASE WHEN A.CountType = 0 THEN '收入' WHEN A.CountType = 1 THEN '支出' ELSE '利润' END countTypeName")
                    //.where("A.YearCount !=0")
                    //.setYearNum(Integer.parseInt(yearNum))
                    .where(whereString)
                    .setOrderby("A.CountType ASC,A.AccountType ASC,A.DealType ASC")
                    .queryMap();
             */


            //处理数据
            ExportExcelUtils<ReDeptRevenueExpenditure> util = new ExportExcelUtils<>(new ReDeptRevenueExpenditure());
            String toFilePath = util.exportExcel(listData, listField, "交易类型业务收支报告");
            UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
            return R.ok(dto.getUrl());
        }catch (Exception e){
            logger.error("导出部门业务收支出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }


}